package cn.lizemin.toExcel;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.*;


/**
 * @author: lizemin
 * @version: 1.0
 */
public class ExportToExcel {
    //这是一个main方法,是程序的入口
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        /*Class.forName("com.mysql.jdbc.Driver");//可以省略不写,因为从MySQL5以后会自动帮我们注册驱动
        //3.获取数据库连接对象
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3308/dream", "root", "123123");
        //4.定义sql语句
        String sql = "select * from `user`;";
        //5.获取执行sql的对象
        Statement sta = con.createStatement();
        //6.执行sql
        ResultSet resultSet = sta.executeQuery(sql);
        exportToExcel(resultSet);*/
        HashMap<String, Object> hm = new HashMap<>();
        ArrayList<Integer> list1 = new ArrayList<>();
        list1.add(12);
        list1.add(13);
        list1.add(14);
        list1.add(15);

        ArrayList<Integer> list2 = new ArrayList<>();
        list2.add(22);
        list2.add(23);
        list2.add(24);
        list2.add(25);

        hm.put("服务器名称",list1);
        hm.put("服务器id",list2);
        exportToExcel(hm);


    }

    public static void exportToExcel(Map<String,Object> objData) throws IOException, SQLException {
        HSSFWorkbook wb = new HSSFWorkbook();
        // Create an HSSFSheet object
        LocalDate now = LocalDate.now();
        DateTimeFormatter df = DateTimeFormatter.ISO_LOCAL_DATE;
        String str_date = df.format(now);
        HSSFSheet sheet = wb.createSheet(str_date);

        System.out.println("入参：" + objData);

        Set<String> keySet = objData.keySet();


        //先打印字段
        /*int column_num = keySet.size();
        for (int k = 0; k < column_num; k++) {

            Iterator<String> it = keySet.iterator();
            while (it.hasNext()) {
                String column_name = it.next();
//                System.out.println("获取字段：" + column_name);
                sheet.createRow(0).createCell(k).setCellValue(column_name);

            }

        }*/

        Iterator<String> it = keySet.iterator();
        int i = 0;
        int j = 0;
        HSSFRow filed_column = sheet.createRow(0);
        while (it.hasNext()){
            String column_name = it.next();
            System.out.println("获取字段：" + column_name);
            filed_column.createCell(j++).setCellValue(column_name);

            //打印值
            List list = (List) objData.get(column_name);
            System.out.println("每个字段下的值：" + list);
            //将值打印到一列中
            //1 0   1
            //2 0   2
            //3 0   3
            int column_num = keySet.size();
            int value_count = list.size();
            for (int h = 0; h < column_num; h++) {
                //第一列
                for (int k = 0; k < value_count; k++) {
                    sheet.createRow(i + 1).createCell(h).setCellValue(String.valueOf(list.get(k)));

                }
            }


            //第二列
            /**
             * 1    0   1
             * 2    0   2
             */
            /*for (int k = 0; k < value_count; k++) {
                sheet.createRow(i + 1).createCell(0).setCellValue(String.valueOf(list.get(k)));

            }*/


            System.out.println("--------------");

            i = 0;

        }

        /*while (it.hasNext()){
            String column_name = it.next();
            System.out.println("获取字段：" + column_name);
            filed_column.createCell(j++).setCellValue(column_name);

            //打印值
            List list = (List) objData.get(column_name);
            System.out.println("每个字段下的值：" + list);

            System.out.println("--------------");

//            i++;
        }*/

        //获取记录数


        /*for (String s : keySet) {
            *//**
             * 行    列
             * 0    1
             *//*
            //字段名
            sheet.createRow(i).createCell(j++).setCellValue(s);
        }*/

        LocalDateTime nowDateTime = LocalDateTime.now();

        DateTimeFormatter df2 = DateTimeFormatter.ISO_LOCAL_DATE_TIME;

        String str_nowDateTime = df2.format(nowDateTime);
        System.out.println(str_nowDateTime);
        str_nowDateTime = str_nowDateTime.replaceAll("-", "").replaceAll(":", "");
        StringBuilder stringBuilder = new StringBuilder(str_nowDateTime);

        StringBuilder sb = stringBuilder.replace(str_nowDateTime.indexOf("."), str_nowDateTime.indexOf(".") + 1, "");

        System.out.println(sb);
        FileOutputStream output = new FileOutputStream("C:\\Users\\15871\\Desktop\\mybatis\\" + sb + ".xls");

        wb.write(output);
        output.flush();

        //关闭流
        output.close();

    }

    public static void exportToExcel(ResultSet objData) throws IOException, SQLException {
        HSSFWorkbook wb = new HSSFWorkbook();
        // Create an HSSFSheet object
        LocalDate now = LocalDate.now();
        DateTimeFormatter df = DateTimeFormatter.ISO_LOCAL_DATE;
        String str_date = df.format(now);
        HSSFSheet sheet = wb.createSheet(str_date);

        //通过结果集获取列数和记录数，必须将指针先放在最后面
        objData.last();

        int columnCount = objData.getMetaData().getColumnCount();

//        System.out.println(columnCount);
        int rowCount = objData.getRow();
//        System.out.println(rowCount);
        //将列名进行打印
        HSSFRow row_field = sheet.createRow(0);
        for (int i = 0; i < columnCount; i++) {//列数
            row_field.createCell(i).setCellValue(objData.getMetaData().getColumnName(i + 1));
        }

        //先打印一行数据,将指针移到最前面
        //通过结果集来遍历取出数据，必须将指针先放在最前面
        objData.first();

        for (int i = 0; i < rowCount; i++) {//打印多行数据
            HSSFRow row_data = sheet.createRow(i + 1);
            //每一行中又有多列数据
            for (int j = 0; j < columnCount; j++) {
                row_data.createCell(j).setCellValue(objData.getString(j + 1));
            }
            //移动指针
            objData.next();

        }

        //将文件名弄成时间戳
        LocalDateTime nowDateTime = LocalDateTime.now();

        DateTimeFormatter df2 = DateTimeFormatter.ISO_LOCAL_DATE_TIME;

        String str_nowDateTime = df2.format(nowDateTime);
        System.out.println(str_nowDateTime);
        str_nowDateTime = str_nowDateTime.replaceAll("-", "").replaceAll(":", "");
        StringBuilder stringBuilder = new StringBuilder(str_nowDateTime);

        StringBuilder sb = stringBuilder.replace(str_nowDateTime.indexOf("."), str_nowDateTime.indexOf(".") + 1, "");

        System.out.println(sb);
        FileOutputStream output = new FileOutputStream("C:\\Users\\15871\\Desktop\\mybatis\\" + sb + ".xls");

        wb.write(output);
        output.flush();

    }

}
